﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using WpfSnqkGasAnalysis.IData;
using WpfSnqkGasAnalysis.Model;

namespace WpfSnqkGasAnalysis.IData_impl
{
    /// <summary>
    /// 数据接口实现。有机硫
    /// </summary>
    /// 创建时间：2022-12-26 17:56:53。作者：王浩力
    public class Organic_sulfurDAL_impl : BaseDAL, IOrganic_sulfurDAL
    {
        public IResult Add(Organic_sulfur organic_Sulfur)
        {
            string sql = @$" INSERT INTO organic_sulfur (
                               Id,
                               Samp_no,
                               Well_deep,
                               Sampling_conditions,
                               Client,
                               Sampling_user_name,
                               Sampling_unit,
                               Well_no,
                               Horizon,
                               Oil_pressure,
                               Sampling_time,
                               Analysis_time,
                               Analysis_unit,
                               Sampling_location,
                               Sampling_temp,
                               Casing_pressure,
                               Cos,
                               Dimethyl_Sulfide,
                               Tert_butyl,
                               Diethyl_sulphide,
                               Methyl_mercaptan,
                               Carbon_disulfide,
                               Methylethyl_sulfide,
                               N_Butyl_mercaptan,
                               Ethanethiol,
                               Isopropanethiol,
                               Thiophene,
                               Dithioether,
                               Organic_sulfur_total,
                               Remark,
                               StepS,
                               Createtime,
                               CreateUser,
                               CreateUserId  ,
                                Analysis_userID
                           )
                           VALUES (
                               $Id,
                               $Samp_no,
                               $Well_deep,
                               $Sampling_conditions,
                               $Client,
                               $Sampling_user_name,
                               $Sampling_unit,
                               $Well_no,
                               $Horizon,
                               $Oil_pressure,
                               $Sampling_time,
                               $Analysis_time,
                               $Analysis_unit,
                               $Sampling_location,
                               $Sampling_temp,
                               $Casing_pressure,
                               $Cos,
                               $Dimethyl_Sulfide,
                               $Tert_butyl,
                               $Diethyl_sulphide,
                               $Methyl_mercaptan,
                               $Carbon_disulfide,
                               $Methylethyl_sulfide,
                               $N_Butyl_mercaptan,
                               $Ethanethiol,
                               $Isopropanethiol,
                               $Thiophene,
                               $Dithioether,
                               $Organic_sulfur_total,
                               $Remark,
                               $StepS,
                               $Createtime,
                               $CreateUser,
                               $CreateUserId ,
                                $Analysis_userID
                           );   ";
            Snowflake snowflake = new Snowflake(1, 1);
            Dictionary<string, object> kv = new Dictionary<string, object>();
            kv.Add("$Id", snowflake.NextId());
            kv.Add("$Samp_no", organic_Sulfur.Samp_no);
            kv.Add("$Well_deep", organic_Sulfur.Well_deep);
            kv.Add("$Sampling_conditions", organic_Sulfur.Sampling_conditions);
            kv.Add("$Client", organic_Sulfur.Client);
            kv.Add("$Sampling_user_name", organic_Sulfur.Sampling_user_name);
            kv.Add("$Sampling_unit", organic_Sulfur.Sampling_unit);
            kv.Add("$Well_no", organic_Sulfur.Well_no);
            kv.Add("$Horizon", organic_Sulfur.Horizon);
            kv.Add("$Oil_pressure", organic_Sulfur.Oil_pressure);
            kv.Add("$Sampling_time", organic_Sulfur.Sampling_time);
            kv.Add("$Analysis_time", organic_Sulfur.Analysis_time);
            kv.Add("$Analysis_unit", organic_Sulfur.Analysis_unit);
            kv.Add("$Sampling_location", organic_Sulfur.Sampling_location);
            kv.Add("$Sampling_temp", organic_Sulfur.Sampling_temp);
            kv.Add("$Casing_pressure", organic_Sulfur.Casing_pressure);
            kv.Add("$Cos", organic_Sulfur.Cos);
            kv.Add("$Dimethyl_Sulfide", organic_Sulfur.Dimethyl_Sulfide);
            kv.Add("$Tert_butyl", organic_Sulfur.Tert_butyl);
            kv.Add("$Diethyl_sulphide", organic_Sulfur.Diethyl_sulphide);
            kv.Add("$Methyl_mercaptan", organic_Sulfur.Methyl_mercaptan);
            kv.Add("$Carbon_disulfide", organic_Sulfur.Carbon_disulfide);
            kv.Add("$Methylethyl_sulfide", organic_Sulfur.Methylethyl_sulfide);
            kv.Add("$N_Butyl_mercaptan", organic_Sulfur.N_Butyl_mercaptan);
            kv.Add("$Ethanethiol", organic_Sulfur.Ethanethiol);
            kv.Add("$Isopropanethiol", organic_Sulfur.Isopropanethiol);
            kv.Add("$Thiophene", organic_Sulfur.Thiophene);
            kv.Add("$Dithioether", organic_Sulfur.Dithioether);
            kv.Add("$Organic_sulfur_total", organic_Sulfur.Organic_sulfur_total);
            kv.Add("$Remark", organic_Sulfur.Remark);
            kv.Add("$StepS", organic_Sulfur.StepS);
            kv.Add("$Createtime", DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss.fff"));
            kv.Add("$CreateUser", organic_Sulfur.CreateUser);
            kv.Add("$CreateUserId", organic_Sulfur.CreateUserId);
            //kv.Add("$Proofreader", organic_Sulfur.Proofreader);
            //kv.Add("$ProofreaderTime", organic_Sulfur.ProofreaderTime);

            //分析人id。添加时间：2023-1-9 17:06:08
            kv.Add("$Analysis_userID", organic_Sulfur.Analysis_userID);

            string error = null;
            int count = ExecSqlCmd(sql, kv, c =>
                              {
                                  return c.ExecuteNonQuery();
                              }, ex => { error = ex.Message; });
            return count > 0 ? new ResultSuccess("新增成功") : new Result("新增失败," + error);
        }

        public IResult Check(Organic_sulfur_check_history check_History)
        {
            int step = 4;//审核通过
            if (check_History.Check_stat == 0)
            {
                step = 3;//驳回
            }
            string sql = $" update  organic_sulfur  set  StepS={step}  where  id={check_History.Organic_sulfurtId_ID}; ";
            sql += @$" INSERT INTO organic_sulfur_check_history (
                                             Id,
                                             Check_user_name,
                                             Check_user_id,
                                             Check_time,
                                             Check_stat,
                                             Reject_reason,
                                             Organic_sulfurtId_ID
                                         )
                                         VALUES (
                                             $Id,
                                             $Check_user_name,
                                             $Check_user_id,
                                             $Check_time,
                                             $Check_stat,
                                             $Reject_reason,
                                             $Organic_sulfurtId_ID
                                         );      ";
            Dictionary<string, object> kv = new Dictionary<string, object>();
            Snowflake snowflake = new Snowflake(1, 1);
            kv.Add("$Id", snowflake.NextId());
            kv.Add("$Check_user_name", check_History.Check_user_name);
            kv.Add("$Check_user_id", check_History.Check_user_id);
            kv.Add("$Check_time", check_History.Check_time);
            kv.Add("$Check_stat", check_History.Check_stat);
            kv.Add("$Reject_reason", check_History.Reject_reason);
            kv.Add("$Organic_sulfurtId_ID", check_History.Organic_sulfurtId_ID);
            string error = null;
            int count = ExecSqlCmd(sql, kv, c =>
            {
                return c.ExecuteNonQuery();
            }, ex => { error = ex.Message; });
            return count > 0 ? new ResultSuccess("审核成功") : new Result("审核失败," + error);
        }

        public IResult Delete(long organic_SulfurKEY)
        {
            string sql = $@" update  organic_Sulfur  set  DeleteTime='{DateTime.Now.ToString("yyyy-MM-dd HH:mm:ss")}'  where id={organic_SulfurKEY};  ";
            string error = null;
            int count = ExecSqlCmd(sql, null, c =>
            {
                return c.ExecuteNonQuery();
            }, ex => { error = ex.Message; });
            return count > 0 ? new ResultSuccess("删除成功") : new Result("删除失败," + error);
        }

        public IResult Edit(Organic_sulfur organic_Sulfur)
        {
            string sql = $@" UPDATE  organic_sulfur
   SET  
       Samp_no = $Samp_no,
       Well_deep = $Well_deep,
       Sampling_conditions = $Sampling_conditions,
       Client = $Client,
       Sampling_user_name = $Sampling_user_name,
       Sampling_unit = $Sampling_unit,
       Well_no = $Well_no,
       Horizon = $Horizon,
       Oil_pressure = $Oil_pressure,
       Sampling_time = $Sampling_time,
       Analysis_time = $Analysis_time,
       Analysis_unit = $Analysis_unit,
       Sampling_location = $Sampling_location,
       Sampling_temp = $Sampling_temp,
       Casing_pressure = $Casing_pressure,
       Cos = $Cos,
       Dimethyl_Sulfide = $Dimethyl_Sulfide,
       Tert_butyl = $Tert_butyl,
       Diethyl_sulphide = $Diethyl_sulphide,
       Methyl_mercaptan = $Methyl_mercaptan,
       Carbon_disulfide = $Carbon_disulfide,
       Methylethyl_sulfide = $Methylethyl_sulfide,
       N_Butyl_mercaptan = $N_Butyl_mercaptan,
       Ethanethiol = $Ethanethiol,
       Isopropanethiol = $Isopropanethiol,
       Thiophene = $Thiophene,
       Dithioether = $Dithioether,
       Organic_sulfur_total = $Organic_sulfur_total,
       Remark = $Remark   ,
Analysis_userID=$Analysis_userID
 WHERE Id = {organic_Sulfur.Id};          ";

            Dictionary<string, object> kv = new Dictionary<string, object>();
            kv.Add("$Samp_no", organic_Sulfur.Samp_no);
            kv.Add("$Well_deep", organic_Sulfur.Well_deep);
            kv.Add("$Sampling_conditions", organic_Sulfur.Sampling_conditions);
            kv.Add("$Client", organic_Sulfur.Client);
            kv.Add("$Sampling_user_name", organic_Sulfur.Sampling_user_name);
            kv.Add("$Sampling_unit", organic_Sulfur.Sampling_unit);
            kv.Add("$Well_no", organic_Sulfur.Well_no);
            kv.Add("$Horizon", organic_Sulfur.Horizon);
            kv.Add("$Oil_pressure", organic_Sulfur.Oil_pressure);
            kv.Add("$Sampling_time", organic_Sulfur.Sampling_time);
            kv.Add("$Analysis_time", organic_Sulfur.Analysis_time);
            kv.Add("$Analysis_unit", organic_Sulfur.Analysis_unit);
            kv.Add("$Sampling_location", organic_Sulfur.Sampling_location);
            kv.Add("$Sampling_temp", organic_Sulfur.Sampling_temp);
            kv.Add("$Casing_pressure", organic_Sulfur.Casing_pressure);
            kv.Add("$Cos", organic_Sulfur.Cos);
            kv.Add("$Dimethyl_Sulfide", organic_Sulfur.Dimethyl_Sulfide);
            kv.Add("$Tert_butyl", organic_Sulfur.Tert_butyl);
            kv.Add("$Diethyl_sulphide", organic_Sulfur.Diethyl_sulphide);
            kv.Add("$Methyl_mercaptan", organic_Sulfur.Methyl_mercaptan);
            kv.Add("$Carbon_disulfide", organic_Sulfur.Carbon_disulfide);
            kv.Add("$Methylethyl_sulfide", organic_Sulfur.Methylethyl_sulfide);
            kv.Add("$N_Butyl_mercaptan", organic_Sulfur.N_Butyl_mercaptan);
            kv.Add("$Ethanethiol", organic_Sulfur.Ethanethiol);
            kv.Add("$Isopropanethiol", organic_Sulfur.Isopropanethiol);
            kv.Add("$Thiophene", organic_Sulfur.Thiophene);
            kv.Add("$Dithioether", organic_Sulfur.Dithioether);
            kv.Add("$Organic_sulfur_total", organic_Sulfur.Organic_sulfur_total);
            kv.Add("$Remark", organic_Sulfur.Remark);

            //分析人id。添加时间：2023-1-9 17:04:50
            kv.Add("$Analysis_userID", organic_Sulfur.Analysis_userID);

            string error = null;
            int count = ExecSqlCmd(sql, kv, c =>
            {
                return c.ExecuteNonQuery();
            }, ex => { error = ex.Message; });
            return count > 0 ? new ResultSuccess("修改成功") : new Result("修改失败," + error);
        }

        public Organic_sulfur Get(long id)
        {
            string sql = "select  *   from   Organic_sulfur where  id=" + id;
            return ExecSqlCmd(sql, null, c =>
            {
                var reader = c.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                Organic_sulfur info = null;
                if (reader.Read())
                {
                    info = ReaderLoadField(reader);
                }
                return info;
            });
        }

        public PageModel<Organic_sulfur> Page(Organic_sulfur organic_Sulfur, int p, int rows)
        {
            PageModel<Organic_sulfur> pageData = new PageModel<Organic_sulfur>();
            Dictionary<string, object> kv = new Dictionary<string, object>();
            StringBuilder sb = new StringBuilder();
            if (organic_Sulfur != null)
            {
                if (!string.IsNullOrWhiteSpace(organic_Sulfur.Samp_no))
                {
                    sb.AppendLine("  and  Samp_no  like  $Samp_no");
                    kv.Add("$Samp_no", "%" + organic_Sulfur.Samp_no + "%");
                }

                //步骤值查询 //添加时间：2022-12-29 22:54:57
                if (organic_Sulfur.WorkList?.Count > 0)
                {
                    List<string> stepList = new List<string>();
                    foreach (var item in organic_Sulfur.WorkList)
                    {
                        stepList.Add($"  StepS={item} ");
                    }
                    string step = $"  and  ({ string.Join(" or  ", stepList)  })  ";
                    sb.AppendLine(step);
                }
            }
            //总记录数
            string sqlTotal = $" select  count(0) from  organic_Sulfur  where  DeleteTime is null  {sb.ToString()} ;";
            pageData.Total = ExecSqlCmd(sqlTotal, kv, c =>
             {
                 var obj = c.ExecuteScalar();
                 return (int)obj.ToMyInt64();
             });

            string sql = @$"   select *  
,(select Check_user_name from organic_sulfur_check_history  where  Organic_sulfurtId_ID=c.id  order by id desc limit 1) as check_user_name   
,(select Check_time           from organic_sulfur_check_history  where  Organic_sulfurtId_ID=c.id  order by id desc limit 1) as check_time   
   
from  organic_Sulfur  as  c  where  DeleteTime is null  {sb.ToString()}   order by id desc  limit   {rows}  offset  {(p - 1) * rows}    ; ";
            pageData.List = base.ExecSqlCmd(sql, kv, c =>
            {
                var reader = c.ExecuteReader();
                if (!reader.HasRows)
                {
                    return null;
                }
                List<Organic_sulfur> list = new List<Organic_sulfur>();
                while (reader.Read())
                {
                    Organic_sulfur info = ReaderLoadField(reader);
                    info.E3 = reader["check_user_name"].ToString();
                    info.E4 = reader["check_time"].ToString();

                    list.Add(info);
                }
                return list;
            });
            return pageData;
        }

        /// <summary>
        /// 读取字段值
        /// </summary>
        /// <param name="reader"></param>
        /// <returns></returns>
        private static Organic_sulfur ReaderLoadField(System.Data.Common.DbDataReader reader)
        {
            Organic_sulfur info = new Organic_sulfur();
            info.Id = (long)reader["Id"].ToMyInt64();
            info.Samp_no = reader["Samp_no"].ToString();
            info.Well_deep = reader["Well_deep"].ToString();
            info.Sampling_conditions = reader["Sampling_conditions"].ToString();
            info.Client = reader["Client"].ToString();
            info.Sampling_user_name = reader["Sampling_user_name"].ToString();
            info.Sampling_unit = reader["Sampling_unit"].ToString();
            info.Well_no = reader["Well_no"].ToString();
            info.Horizon = reader["Horizon"].ToString();

            info.Oil_pressure = reader["Oil_pressure"].ToMyDecimal();
            info.Sampling_time = reader["Sampling_time"].ToString();
            info.Analysis_time = reader["Analysis_time"].ToString();
            info.Analysis_unit = reader["Analysis_unit"].ToString();
            info.Sampling_location = reader["Sampling_location"].ToString();

            info.Sampling_temp = reader["Sampling_temp"].ToMyDecimal();
            info.Casing_pressure = reader["Casing_pressure"].ToMyDecimal();
            info.Cos = reader["Cos"].ToMyDecimal();
            info.Dimethyl_Sulfide = reader["Dimethyl_Sulfide"].ToMyDecimal();
            info.Tert_butyl = reader["Tert_butyl"].ToMyDecimal();
            info.Diethyl_sulphide = reader["Diethyl_sulphide"].ToMyDecimal();
            info.Methyl_mercaptan = reader["Methyl_mercaptan"].ToMyDecimal();
            info.Carbon_disulfide = reader["Carbon_disulfide"].ToMyDecimal();

            info.Methylethyl_sulfide = reader["Methylethyl_sulfide"].ToMyDecimal();
            info.N_Butyl_mercaptan = reader["N_Butyl_mercaptan"].ToMyDecimal();
            info.Ethanethiol = reader["Ethanethiol"].ToMyDecimal();
            info.Isopropanethiol = reader["Isopropanethiol"].ToMyDecimal();
            info.Thiophene = reader["Thiophene"].ToMyDecimal();
            info.Dithioether = reader["Dithioether"].ToMyDecimal();
            info.Organic_sulfur_total = reader["Organic_sulfur_total"].ToMyDecimal();

            info.Remark = reader["Remark"].ToString();
            info.StepS = (byte)reader["StepS"].ToMyDecimal();

            info.Createtime = reader["Createtime"].ToString();
            info.CreateUser = reader["CreateUser"].ToString();
            info.CreateUserId = (long)reader["CreateUserId"].ToMyInt64();
            info.Proofreader = reader["Proofreader"].ToString();
            info.ProofreaderTime = reader["ProofreaderTime"].ToString();
            info.DeleteTime = reader["DeleteTime"].ToString();

            //分析人id。添加时间：2023-1-9 17:07:04
            info.Analysis_userID = reader["Analysis_userID"].ToMyInt64();

            //校对人id
            info.ProofreaderUserId = reader["ProofreaderUserId"].ToMyInt64();
            return info;
        }

        public IResult Proofreader(Organic_sulfur organic_Sulfur)
        {
            string sql = $@" UPDATE  organic_sulfur
   SET  
       Samp_no = $Samp_no,
       Well_deep = $Well_deep,
       Sampling_conditions = $Sampling_conditions,
       Client = $Client,
       Sampling_user_name = $Sampling_user_name,
       Sampling_unit = $Sampling_unit,
       Well_no = $Well_no,
       Horizon = $Horizon,
       Oil_pressure = $Oil_pressure,
       Sampling_time = $Sampling_time,
       Analysis_time = $Analysis_time,
       Analysis_unit = $Analysis_unit,
       Sampling_location = $Sampling_location,
       Sampling_temp = $Sampling_temp,
       Casing_pressure = $Casing_pressure,
       Cos = $Cos,
       Dimethyl_Sulfide = $Dimethyl_Sulfide,
       Tert_butyl = $Tert_butyl,
       Diethyl_sulphide = $Diethyl_sulphide,
       Methyl_mercaptan = $Methyl_mercaptan,
       Carbon_disulfide = $Carbon_disulfide,
       Methylethyl_sulfide = $Methylethyl_sulfide,
       N_Butyl_mercaptan = $N_Butyl_mercaptan,
       Ethanethiol = $Ethanethiol,
       Isopropanethiol = $Isopropanethiol,
       Thiophene = $Thiophene,
       Dithioether = $Dithioether,
       Organic_sulfur_total = $Organic_sulfur_total,
       Remark = $Remark,     
       StepS=$StepS,
ProofreaderUserId=$ProofreaderUserId,
        Proofreader = $Proofreader,
        ProofreaderTime = $ProofreaderTime   ,
Analysis_userID=$Analysis_userID
 WHERE Id = {organic_Sulfur.Id};          ";

            Dictionary<string, object> kv = new Dictionary<string, object>();
            kv.Add("$Samp_no", organic_Sulfur.Samp_no);
            kv.Add("$Well_deep", organic_Sulfur.Well_deep);
            kv.Add("$Sampling_conditions", organic_Sulfur.Sampling_conditions);
            kv.Add("$Client", organic_Sulfur.Client);
            kv.Add("$Sampling_user_name", organic_Sulfur.Sampling_user_name);
            kv.Add("$Sampling_unit", organic_Sulfur.Sampling_unit);
            kv.Add("$Well_no", organic_Sulfur.Well_no);
            kv.Add("$Horizon", organic_Sulfur.Horizon);
            kv.Add("$Oil_pressure", organic_Sulfur.Oil_pressure);
            kv.Add("$Sampling_time", organic_Sulfur.Sampling_time);
            kv.Add("$Analysis_time", organic_Sulfur.Analysis_time);
            kv.Add("$Analysis_unit", organic_Sulfur.Analysis_unit);
            kv.Add("$Sampling_location", organic_Sulfur.Sampling_location);
            kv.Add("$Sampling_temp", organic_Sulfur.Sampling_temp);
            kv.Add("$Casing_pressure", organic_Sulfur.Casing_pressure);
            kv.Add("$Cos", organic_Sulfur.Cos);
            kv.Add("$Dimethyl_Sulfide", organic_Sulfur.Dimethyl_Sulfide);
            kv.Add("$Tert_butyl", organic_Sulfur.Tert_butyl);
            kv.Add("$Diethyl_sulphide", organic_Sulfur.Diethyl_sulphide);
            kv.Add("$Methyl_mercaptan", organic_Sulfur.Methyl_mercaptan);
            kv.Add("$Carbon_disulfide", organic_Sulfur.Carbon_disulfide);
            kv.Add("$Methylethyl_sulfide", organic_Sulfur.Methylethyl_sulfide);
            kv.Add("$N_Butyl_mercaptan", organic_Sulfur.N_Butyl_mercaptan);
            kv.Add("$Ethanethiol", organic_Sulfur.Ethanethiol);
            kv.Add("$Isopropanethiol", organic_Sulfur.Isopropanethiol);
            kv.Add("$Thiophene", organic_Sulfur.Thiophene);
            kv.Add("$Dithioether", organic_Sulfur.Dithioether);
            kv.Add("$Organic_sulfur_total", organic_Sulfur.Organic_sulfur_total);
            kv.Add("$Remark", organic_Sulfur.Remark);
            kv.Add("$StepS", organic_Sulfur.StepS);
            kv.Add("$Proofreader", organic_Sulfur.Proofreader);
            kv.Add("$ProofreaderTime", organic_Sulfur.ProofreaderTime);

            //校对人id。添加时间：2023-1-9 17:02:10
            kv.Add("$ProofreaderUserId", organic_Sulfur.ProofreaderUserId);

            //分析人id。添加时间：2023-1-9 17:02:10
            kv.Add("$Analysis_userID", organic_Sulfur.Analysis_userID);

            string error = null;
            int count = ExecSqlCmd(sql, kv, c =>
            {
                return c.ExecuteNonQuery();
            }, ex => { error = ex.Message; });
            return count > 0 ? new ResultSuccess("校对成功") : new Result("校对失败," + error);

        }
    }
}
